package com.zzb.codegenerator.generator.dctm.mysql;


import com.zzb.codegenerator.bean.TableColumn;
import com.zzb.codegenerator.bean.TableMeta;
import com.zzb.codegenerator.config.GeneratorConfig;
import com.zzb.codegenerator.generator.connect.ConnectPool;
import com.zzb.codegenerator.generator.context.TableMetaContext;
import com.zzb.codegenerator.generator.dctm.DbToTableMetaConvertor;
import com.zzb.codegenerator.generator.table.ITableMetaFoctory;
import com.zzb.codegenerator.utils.GetJdbcUrlUtil;
import com.zzb.codegenerator.utils.MySQLTypeToJavaType;
import com.zzb.codegenerator.utils.OperatorEnvUtils;
import com.zzb.codegenerator.utils.PostgreSQLToJavaType;

import java.sql.*;
import java.util.*;

/**
 * Created by ZZB on 2017/12/4.
 */
public class MysqlDbToTableMetaConvertor implements DbToTableMetaConvertor {
    private GeneratorConfig generatorConfig;
    private static final String MysqlTableMetaSQL =
            "SELECT\n" +
                    "  `COLUMN_NAME`                  AS columnName,\n" +
                    "  UPPER(DATA_TYPE)               AS columnType,\n" +
                    "  CHARACTER_MAXIMUM_LENGTH       AS maxLength,\n" +
                    "  NUMERIC_PRECISION              AS `precision`,\n" +
                    "  NUMERIC_SCALE                  AS scale,\n" +
                    "  (IS_NULLABLE = 'YES')          AS isNullable,\n" +
                    "  (COLUMN_KEY = 'PRI')           AS isPrimaryKey,\n" +
                    "  (COLUMN_TYPE LIKE '%unsigned') AS isSigned,\n" +
                    "  COLUMN_DEFAULT                 AS defaultValue,\n" +
                    "  COLUMN_COMMENT                 AS columnComment,\n" +
                    "  UPPER(COLUMN_TYPE)                    AS typeFullName\n" +
                    "FROM information_schema.columns\n" +
                    "WHERE table_schema = '{dbName}'\n" +
                    "      AND table_name = '{tableName}';";
    private static final String mysqlTableCommentSQL =
            "SELECT table_name,table_comment FROM information_schema.TABLES WHERE table_schema='{dbName}' and table_name = '{tableName}';\n";

    public MysqlDbToTableMetaConvertor(GeneratorConfig generatorConfig) {
        this.generatorConfig = generatorConfig;
    }

    @Override
    public TableMeta convertFromTableName(String tableName) throws Exception {
        TableMeta tableMeta = new TableMeta();
        tableMeta.setTableName(tableName);

        List<TableColumn> list = new ArrayList<>();

        //获取连接
        Connection connection = ConnectPool.getConnection(GetJdbcUrlUtil.getDriverName(generatorConfig.getJdbcUrl()),
                generatorConfig.getJdbcUrl(), generatorConfig.getJdbcUsername(), generatorConfig.getJdbcPassword());
        if (connection == null) throw new Exception("数据库连接失败");

        String schema = connection.getCatalog();

        String sql = MysqlTableMetaSQL.replace("{tableName}", tableName).replace("{dbName}", schema);
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        tableMeta.setPrimaryKeyColumns(new ArrayList<>());

        while (rs.next()) {
            TableColumn tableColumn = new TableColumn();
            tableColumn.setColumnName(rs.getString("columnName"));
            tableColumn.setColumnType(rs.getString("columnType"));
            tableColumn.setColumnComment(rs.getString("columnComment"));
            tableColumn.setMaxLength(rs.getLong("maxLength"));
            tableColumn.setPrecision(rs.getLong("precision"));
            tableColumn.setScale(rs.getLong("scale"));
            tableColumn.setNullable(rs.getBoolean("isNullable"));
            tableColumn.setPrimaryKey(rs.getBoolean("isPrimaryKey"));
            tableColumn.setUnsigned(false);
            tableColumn.setDefaultValue(rs.getString("defaultValue"));
            tableColumn.setColumnTypeFullName(rs.getString("typeFullName"));
            try {
                tableColumn.setPropertyType(MySQLTypeToJavaType.mysql2java.get(tableColumn.getColumnType()).getJavaType());
                tableColumn.setPropertyFullType(MySQLTypeToJavaType.mysql2java.get(tableColumn.getColumnType()).getJavaFullType());
                tableColumn.setJdbcType(MySQLTypeToJavaType.mysql2java.get(tableColumn.getColumnType()).getMysqlType().getName());
            } catch (Exception e) {
                System.out.println("类型转换异常！[{name}]-[{type}]"
                        .replace("{name}", tableColumn.getColumnName())
                        .replace("{type}", tableColumn.getColumnType()));
            }
            tableColumn.setPropertyName(ITableMetaFoctory.rename(generatorConfig.getNameType(), tableColumn.getColumnName()));
            list.add(tableColumn);
            if (tableColumn.getPrimaryKey()) {
                tableMeta.getPrimaryKeyColumns().add(tableColumn);
            }
        }

        String tableCommentSql = mysqlTableCommentSQL.replace("{tableName}", tableName).replace("{dbName}", schema);
        ps = connection.prepareStatement(tableCommentSql);
        rs = ps.executeQuery();
        if(rs.next()){
            tableMeta.setTableComment(rs.getString("table_comment"));
        }

        tableMeta.setTableColumns(list);
        tableMeta.setConfig(generatorConfig);
        tableMeta.setTableName(tableName);
        tableMeta.setBeanName(TableMetaContext.getTableMap().get(tableName));
        tableMeta.setSystemUserName(OperatorEnvUtils.getSystemUserName());
        tableMeta.setCurrentTime(new java.util.Date());
        return tableMeta;
    }

    @Override
    public boolean tableExist(Connection connection, String tableName) {
        try {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("select 1 from `" + tableName + "`");
            resultSet.next();
            resultSet.close();
            statement.close();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
}
